# -*- coding: utf-8 -*-
"""
数据检查脚本
用于验证爬取数据的保存和处理情况
"""

import sqlite3
import pandas as pd
from datetime import datetime, timedelta
import os


def check_database_data():
    """检查数据库中的数据"""
    
    db_path = "data/real_estate_leads.db"
    
    if not os.path.exists(db_path):
        print("❌ 数据库文件不存在")
        return
    
    try:
        conn = sqlite3.connect(db_path)
        
        print("📊 数据库统计信息:")
        print("="*50)
        
        # 总数据量
        total_count = pd.read_sql("SELECT COUNT(*) as count FROM real_estate_leads", conn).iloc[0]['count']
        print(f"总数据量: {total_count} 条")
        
        # 按城市统计
        city_stats = pd.read_sql("""
            SELECT city, COUNT(*) as count 
            FROM real_estate_leads 
            GROUP BY city 
            ORDER BY count DESC
        """, conn)
        
        print(f"\n🏙️ 按城市统计:")
        for _, row in city_stats.iterrows():
            print(f"   {row['city']}: {row['count']} 条")
        
        # 按业态统计
        business_stats = pd.read_sql("""
            SELECT business_type, COUNT(*) as count 
            FROM real_estate_leads 
            GROUP BY business_type 
            ORDER BY count DESC
        """, conn)
        
        print(f"\n🏪 按业态统计:")
        for _, row in business_stats.iterrows():
            print(f"   {row['business_type']}: {row['count']} 条")
        
        # 最近数据
        recent_data = pd.read_sql("""
            SELECT city, business_type, contact_person, contact_info, 
                   acceptable_rent, crawl_time
            FROM real_estate_leads 
            ORDER BY crawl_time DESC 
            LIMIT 5
        """, conn)
        
        print(f"\n📋 最新5条数据:")
        for i, row in recent_data.iterrows():
            print(f"   {i+1}. {row['city']} - {row['business_type']}")
            print(f"      联系人: {row['contact_person']}")
            print(f"      联系方式: {row['contact_info']}")
            print(f"      租金: {row['acceptable_rent']}")
            print(f"      抓取时间: {row['crawl_time']}")
            print()
        
        # 数据质量检查
        print(f"📈 数据质量统计:")
        
        # 联系方式完整性
        contact_info_count = pd.read_sql("""
            SELECT COUNT(*) as count 
            FROM real_estate_leads 
            WHERE contact_info IS NOT NULL AND contact_info != ''
        """, conn).iloc[0]['count']
        contact_info_rate = (contact_info_count / total_count * 100) if total_count > 0 else 0
        print(f"   联系方式完整性: {contact_info_rate:.1f}% ({contact_info_count}/{total_count})")
        
        # 联系人完整性
        contact_person_count = pd.read_sql("""
            SELECT COUNT(*) as count 
            FROM real_estate_leads 
            WHERE contact_person IS NOT NULL AND contact_person != ''
        """, conn).iloc[0]['count']
        contact_person_rate = (contact_person_count / total_count * 100) if total_count > 0 else 0
        print(f"   联系人完整性: {contact_person_rate:.1f}% ({contact_person_count}/{total_count})")
        
        # 租金信息完整性
        rent_count = pd.read_sql("""
            SELECT COUNT(*) as count 
            FROM real_estate_leads 
            WHERE acceptable_rent IS NOT NULL AND acceptable_rent != ''
        """, conn).iloc[0]['count']
        rent_rate = (rent_count / total_count * 100) if total_count > 0 else 0
        print(f"   租金信息完整性: {rent_rate:.1f}% ({rent_count}/{total_count})")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 数据库检查失败: {e}")


def check_excel_files():
    """检查Excel输出文件"""
    
    output_dir = "output"
    
    if not os.path.exists(output_dir):
        print("❌ 输出目录不存在")
        return
    
    excel_files = [f for f in os.listdir(output_dir) if f.endswith('.xlsx')]
    
    if not excel_files:
        print("❌ 没有找到Excel文件")
        return
    
    print(f"\n📁 Excel文件列表:")
    print("="*50)
    
    for file in sorted(excel_files, reverse=True):
        file_path = os.path.join(output_dir, file)
        file_size = os.path.getsize(file_path)
        file_time = datetime.fromtimestamp(os.path.getmtime(file_path))
        
        print(f"   📄 {file}")
        print(f"      大小: {file_size/1024:.1f} KB")
        print(f"      修改时间: {file_time.strftime('%Y-%m-%d %H:%M:%S')}")
        
        # 读取Excel内容
        try:
            df = pd.read_excel(file_path)
            print(f"      数据行数: {len(df)} 条")
            print(f"      数据列数: {len(df.columns)} 列")
        except Exception as e:
            print(f"      ❌ 读取失败: {e}")
        
        print()


def check_crawl_tasks():
    """检查爬虫任务记录"""
    
    db_path = "data/real_estate_leads.db"
    
    if not os.path.exists(db_path):
        print("❌ 数据库文件不存在")
        return
    
    try:
        conn = sqlite3.connect(db_path)
        
        # 检查是否有crawl_tasks表
        tables = pd.read_sql("""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='crawl_tasks'
        """, conn)
        
        if len(tables) == 0:
            print("❌ 爬虫任务表不存在")
            conn.close()
            return
        
        # 获取任务记录
        tasks = pd.read_sql("""
            SELECT task_name, status, start_time, end_time, result_message
            FROM crawl_tasks 
            ORDER BY start_time DESC 
            LIMIT 10
        """, conn)
        
        print(f"\n📋 最近10个爬虫任务:")
        print("="*50)
        
        for i, row in tasks.iterrows():
            print(f"   {i+1}. {row['task_name']}")
            print(f"      状态: {row['status']}")
            print(f"      开始时间: {row['start_time']}")
            print(f"      结束时间: {row['end_time']}")
            print(f"      结果: {row['result_message']}")
            print()
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 任务记录检查失败: {e}")


def main():
    """主函数"""
    
    print("🔍 开始检查爬取数据...")
    print("="*60)
    
    # 检查数据库数据
    check_database_data()
    
    # 检查Excel文件
    check_excel_files()
    
    # 检查爬虫任务
    check_crawl_tasks()
    
    print("="*60)
    print("✅ 数据检查完成!")


if __name__ == "__main__":
    main()
